/**
 * 
 */
package krush.rezepte;

import java.util.ArrayList;
import java.util.List;
import strukturen.Kategorie;
import strukturen.Rezept;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

/**
 * @author krush
 * 
 */
public class DBHelper
{
    public final String DBName = "rezepteDB";
    public final String KatTable = "kategorien";
    public final String RezTable = "rezepte";
    public final String SuchTable = "suchbegriffe";

    private Context context;
    private SQLiteDatabase myDB;

    public DBHelper(Context c)
    {
	context = c;
    }

    public void leereTable(String tablename)
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	myDB.delete(tablename, null, null);
	myDB.close();
    }

    public void createKatDB()
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	myDB.execSQL("CREATE TABLE IF NOT EXISTS "
		+ KatTable
		+ " (_id integer primary key autoincrement, name varchar(100), pfad varchar(100))"
		+ ";");
	myDB.close();

    }

    public void createRezDB()
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	myDB.execSQL("CREATE TABLE IF NOT EXISTS "
		+ RezTable
		+ " (_id integer primary key autoincrement, name varchar(200), kat_id integer, link varchar(100), bemerkung varchar(100))"
		+ ";");
	myDB.close();
    }

    public void createSuchDB()
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	myDB.execSQL("CREATE TABLE IF NOT EXISTS " + SuchTable
		+ " (rezept_id integer, name varchar(200))" + ";");
	myDB.close();
    }

    public void insertSuchbegriffe(String line, long pNr)
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	String[] splitted = line.split("\\s+");
	ContentValues values;
	for (int i = 0; i < splitted.length; i++)
	{
	    values = new ContentValues();
	    values.put("rezept_id", pNr);
	    values.put("name", splitted[i]);
	    myDB.insert(SuchTable, null, values);
	}
	myDB.close();
    }

    public long insertKat(String name, String pfad)
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	ContentValues values = new ContentValues();
	values.put("name", name);
	values.put("pfad", pfad);
	long s = myDB.insert(KatTable, null, values);
	myDB.close();
	return s;
    }

    public long insertRez(String name, int kat_id, String link, String bemerkung)
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	ContentValues values = new ContentValues();
	values.put("name", name);
	values.put("link", link);
	values.put("kat_id", kat_id);
	values.put("bemerkung", bemerkung);
	long s = myDB.insert(RezTable, null, values);
	myDB.close();
	return s;
    }

    public ArrayList<Kategorie> getKategorien()
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	ArrayList<Kategorie> kList = new ArrayList<Kategorie>();

	Cursor cur = myDB.rawQuery("SELECT * FROM " + KatTable
		+ " ORDER BY name", null);
	cur.moveToFirst();
	while (cur.isAfterLast() == false)
	{
	    kList.add(new Kategorie(cur.getInt(0), cur.getString(1), cur
		    .getString(2)));
	    cur.moveToNext();
	}
	cur.close();
	myDB.close();
	return kList;

    }

    public String[] getKategorienAsStringArray()
    {

	ArrayList<Kategorie> kl = getKategorien();
	String[] kls = new String[kl.size() + 1];
	kls[0] = "";

	for (int i = 1; i < kl.size() + 1; i++)
	{
	    kls[i] = kl.get(i - 1).getName();
	}

	return kls;
    }

    public String[] getKategorienAsStringArrayOhneLeerstelle()
    {

	ArrayList<Kategorie> kl = getKategorien();
	String[] kls = new String[kl.size()];

	for (int i = 0; i < kl.size(); i++)
	{
	    kls[i] = kl.get(i).getName();
	}

	return kls;
    }

    public String getKategorie(int kat_id)
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	ArrayList<Kategorie> kList = new ArrayList<Kategorie>();

	Cursor cur = myDB.rawQuery("SELECT * FROM " + KatTable
		+ " WHERE _id = " + String.valueOf(kat_id), null);
	cur.moveToFirst();
	while (cur.isAfterLast() == false)
	{
	    kList.add(new Kategorie(cur.getInt(0), cur.getString(1), cur
		    .getString(2)));
	    cur.moveToNext();
	}
	cur.close();
	myDB.close();
	return kList.get(0).getName();
    }

    public ArrayList<Rezept> getRezepte()
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	ArrayList<Rezept> rList = new ArrayList<Rezept>();

	Cursor cur = myDB.rawQuery("SELECT * FROM " + RezTable
		+ " ORDER BY name", null);
	cur.moveToFirst();
	while (cur.isAfterLast() == false)
	{
	    rList.add(new Rezept(cur.getInt(0), cur.getString(1),
		    cur.getInt(2), cur.getString(3), cur.getString(4)));
	    cur.moveToNext();
	}
	cur.close();
	myDB.close();
	return rList;

    }

    public Boolean deleteRezept(long id)
    {
	Boolean success = true;

	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	myDB.delete(SuchTable, "rezept_id=" + id, null);
	int rows = myDB.delete(RezTable, "_id=" + id, null);

	if (rows == 0) success = false;
	myDB.close();
	return success;
    }

    public void deleteSuchbegriffeForRezept(long id)
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	myDB.delete(SuchTable, "rezept_id=" + id, null);
	myDB.close();
    }

    public void updateRezeptName(long id, String neuName)
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	ContentValues args = new ContentValues();
	args.put("name", neuName);
	myDB.update(RezTable, args, "_id =" + id, null);
	myDB.close();
    }

    public void updateRezeptBemerkung(long id, String neuName)
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	ContentValues args = new ContentValues();
	args.put("bemerkung", neuName);
	myDB.update(RezTable, args, "_id =" + id, null);
	myDB.close();
    }

    public void updateRezeptKategorie(long id, int kat_id)
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	ContentValues args = new ContentValues();
	args.put("kat_id", kat_id);
	myDB.update(RezTable, args, "_id =" + id, null);
	myDB.close();
    }

    public Rezept getRezept(long id)
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	ArrayList<Rezept> rList = new ArrayList<Rezept>();

	Cursor cur = myDB.rawQuery("SELECT * FROM " + RezTable
		+ " WHERE _id = " + id + " ORDER BY name", null);
	cur.moveToFirst();
	while (cur.isAfterLast() == false)
	{
	    rList.add(new Rezept(cur.getInt(0), cur.getString(1),
		    cur.getInt(2), cur.getString(3), cur.getString(4)));
	    rList.get(rList.size() - 1).setSuchbegriffe(
		    getSuchbegriffeForRezeptnummer((int) id));
	    cur.moveToNext();
	}
	cur.close();
	myDB.close();
	if (rList.size() == 0)
	    return null;
	else return rList.get(0);

    }

    public String getSuchbegriffeForRezeptnummer(int rNr)
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	String begriffe = "";
	Cursor cur = myDB.rawQuery("SELECT name FROM " + SuchTable
		+ " WHERE rezept_id = " + rNr, null);

	cur.moveToFirst();
	while (cur.isAfterLast() == false)
	{
	    begriffe += cur.getString(0) + " ";
	    cur.moveToNext();
	}
	cur.close();
	myDB.close();
	return begriffe;
    }

    public List<Rezept> getSuche(List<String> begriffe, boolean verkn)
    {
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	List<List<Rezept>> ll = new ArrayList<List<Rezept>>();

	boolean temp = false;
	for (int i = 0; i < begriffe.size(); i++)
	{
	    ll.add(getErgebnisseForBegriff(begriffe.get(i)));
	}

	// TODO HIER MUSS JE NACH VERKNÜPFUNG DIE LISTEN ZU EINER VERBUNDEN
	// WERDEN
	// ODER -> JEDES ELEMENT AUS ALLEN LISTEN (ABER NICHT DOPPELT)
	// UND ->

	// oder
	List<Rezept> rezepte = new ArrayList<Rezept>();
	if (!verkn)
	{
	    boolean vorhanden;
	    for (int i = 0; i < ll.size(); i++)
	    {
		// einzelne Ergebnisliste
		for (int rr = 0; rr < ll.get(i).size(); rr++)
		{
		    // einzelnes Element
		    vorhanden = false;
		    // nachsehen ob vorhanden
		    for (int j = 0; j < rezepte.size(); j++)
		    {
			if (rezepte.get(j).getId() == ll.get(i).get(rr).getId())
			    vorhanden = true;
		    }
		    // wenn nicht vorhanden -> hinzufügen
		    if (!vorhanden) rezepte.add(ll.get(i).get(rr));
		}
	    }
	}
	else
	{
	    boolean inUnterListeVorhanden;
	    boolean inJederUnterListeVorhanden;
	    //erste Liste nehmen und vergleichen
	    
	    if (ll.size() == 1)
	    {
		rezepte = ll.get(0);
	    }
	    else if (ll.size() > 1)
	    {
		for (int i = 0; i < ll.get(0).size(); i++)
		{
		    //jedes Element der Referenzliste
		    inJederUnterListeVorhanden = true;
		    for (int j = 1; j < ll.size(); j++)
		    {			
			//jede andere Liste
			inUnterListeVorhanden = false;
			for (int k = 0; k < ll.get(j).size(); k++)
			{
			    if (ll.get(0).get(i).getId() == ll.get(j).get(k).getId())
				inUnterListeVorhanden = true;
			    //jedes Element der Unterliste
			    
			}
			if (!inUnterListeVorhanden)
			    inJederUnterListeVorhanden = false;
		    }	
		    if (inJederUnterListeVorhanden)
			rezepte.add(ll.get(0).get(i));
		}
	    }
	}

	return rezepte;
    }

    private List<Rezept> getErgebnisseForBegriff(String begriff)
    {
	List<Rezept> r = new ArrayList<Rezept>();
	boolean temp = false;
	// Zuerst durchsuchen der Suchbegriffe
	Cursor cur = myDB.rawQuery("SELECT rezept_id FROM " + SuchTable
		+ " WHERE name LIKE '%" + begriff + "%'", null);
	cur.moveToFirst();
	while (cur.isAfterLast() == false)
	{
	    if (r.size() == 0)
	    {
		r.add(getRezept(cur.getInt(0)));
	    }
	    else
	    {
		temp = false;
		for (int j = 0; j < r.size(); j++)
		{
		    if (r.get(j).getId() == cur.getInt(0)) temp = true;
		}

		if (!temp) r.add(getRezept(cur.getInt(0)));
	    }
	    cur.moveToNext();
	}

	// jetzt durchsuchen der Bezeichnungen
	myDB = context.openOrCreateDatabase(DBName, Context.MODE_PRIVATE, null);
	cur = myDB.rawQuery("SELECT _id FROM " + RezTable
		+ " WHERE name LIKE '%" + begriff + "%'", null);
	cur.moveToFirst();
	while (cur.isAfterLast() == false)
	{
	    if (r.size() == 0)
	    {
		r.add(getRezept(cur.getInt(0)));
	    }
	    else
	    {
		temp = false;
		for (int j = 0; j < r.size(); j++)
		{
		    if (r.get(j).getId() == cur.getInt(0)) temp = true;
		}

		if (!temp) r.add(getRezept(cur.getInt(0)));
	    }
	    cur.moveToNext();
	}

	return r;
    }

    @Override
    public void finalize()
    {
	close();
    }

    public void close()
    {
	if (myDB.isOpen()) myDB.close();

    }

}
